<?php
/**
 * 地図検索用SQL作成クラス<br />
 *
 * @package apamanshop
 * @author SS鈴木
 * @copyright
 */
class Rstobi_Model_ChizuSearch extends Rstobi_Model_Base
{

	/**
	 * 地図検索用FROM句取得(全件取得OEM)
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getFromForChizusearchAllOem($params,&$db_params)
	{

		$sql  ="";
		$sql .="    FROM";
		$sql .="        T03WEB_HEYA T03";
		$sql .="        ,M05TENPO_INFO M05";
		$sql .="        ,M01JYUUSHO_LOCATION M01";

		return $sql;
	}

	/**
	 * 地図検索用WHERE句取得(全件取得OEM)
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getWhereForChizusearchAllOem($params,&$db_params)
	{
		$geomatry = explode(",", $params["geo"]);
		$db_params['Geo1'] = $geomatry[0];
		$db_params['Geo2'] = $geomatry[1];
		$db_params['Geo3'] = $geomatry[2];
		$db_params['Geo4'] = $geomatry[3];

		$sql  ="";
		$sql .="    WHERE";
		$sql .="        SDO_INSIDE(";
		$sql .="            M01.M01_LOCATION";
		$sql .="            ,MDSYS.SDO_GEOMETRY(";
		$sql .="                2003";
		$sql .="                ,8301";
		$sql .="                ,NULL";
		$sql .="                ,MDSYS.SDO_ELEM_INFO_ARRAY(";
		$sql .="                    1";
		$sql .="                    ,1003";
		$sql .="                    ,3";
		$sql .="                )";
		$sql .="                ,MDSYS.SDO_ORDINATE_ARRAY( :Geo1 , :Geo2 , :Geo3 , :Geo4 )";
		$sql .="            )";
		$sql .="        ) = 'TRUE' ";
		$sql .="        AND M05.M05_TENPO_CD = T03.T03_TENPO_CD";
		$sql .="        AND M01.M01_TODOUFUKEN_CD = T03.T03_TODOUFUKEN_CD";
		$sql .="        AND M01.M01_SHIKUCHOUSON_CD = T03.T03_SHIKUCHOUSON_CD";
		$sql .="        AND M01.M01_CHOUMEI_CD = T03.T03_CHOUMEI_CD";
		$sql .="        AND M01.M01_CHOUMOKU_CD = T03.T03_CHOUMOKU_CD";
		$sql .="        AND M01.M01_LOCATION IS NOT NULL";
		if(array_key_exists("OemCd",$params) && $params['OemCd'] != ""){
			// OEMコードが8桁（店舗）
			if(strlen($params['OemCd']) == 8){
				$db_params['OemCd'] = $params["OemCd"];
				$sql .="     AND M05.M05_TENPO_CD  = : OemCd ";
			// OEMコードが4桁（法人）
			}else if(strlen($params['OemCd']) == 4){
				$db_params['OemCd'] = $params["OemCd"];
				$sql .="     AND M05.M05_HOUZIN_CD = : OemCd ";
			}
		}

		return $sql;
	}

	/**
	 * 地図検索用FROM句取得(全件取得)
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getFromForChizusearchAll($params,&$db_params)
	{
		$sql  ="";
		$sql .="    FROM";
		$sql .="        T03WEB_HEYA T03";
		$sql .="        ,T94BUKKEN_MAP T92";

		return $sql;
	}

	/**
	 * 地図検索用WHERE句取得(全件取得)
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getWhereForChizusearchAll($params,&$db_params)
	{
		$geomatry = explode(",", $params["geo"]);
		$db_params['Geo1'] = $geomatry[0];
		$db_params['Geo2'] = $geomatry[1];
		$db_params['Geo3'] = $geomatry[2];
		$db_params['Geo4'] = $geomatry[3];

		$sql  ="";
		$sql .="    WHERE";
		$sql .="        T03.T03_HEYA_CD = T92.T03_HEYA_CD";
		$sql .="        AND SDO_INSIDE(";
		$sql .="            T92.T03_LOCATION";
		$sql .="            ,MDSYS.SDO_GEOMETRY(";
		$sql .="                2003";
		$sql .="                ,8301";
		$sql .="                ,NULL";
		$sql .="                ,MDSYS.SDO_ELEM_INFO_ARRAY(";
		$sql .="                    1";
		$sql .="                    ,1003";
		$sql .="                    ,3";
		$sql .="                )";
		$sql .="                ,MDSYS.SDO_ORDINATE_ARRAY( :Geo1 , :Geo2 , :Geo3 , :Geo4 )";
		$sql .="            )";
		$sql .="        ) = 'TRUE'";

		return $sql;
	}

	/**
	 * 地図検索用FROM句取得
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getFromForChizusearch($params,&$db_params)
	{
		$db_params['PrefCd'] = $params["PrefCd"];
		$db_params['CityCd'] = $params["CityCd"];
		$db_params['TownCd'] = $params["TownCd"];
		$db_params['ChoumokuCd'] = $params["ChoumokuCd"];

		$sql  ="";
		$sql .="   FROM";
		$sql .="       T03WEB_HEYA T03";
		$sql .="       ,M05TENPO_INFO M05";
		$sql .="       ,(";
		$sql .="           SELECT";
		$sql .="                   A.M01_TODOUFUKEN_CD";
		$sql .="                   ,A.M01_SHIKUCHOUSON_CD";
		$sql .="                   ,A.M01_CHOUMEI_CD";
		$sql .="                   ,A.M01_CHOUMOKU_CD";
		$sql .="               FROM";
		$sql .="                   M01JYUUSHO A";
		$sql .="                   ,(";
		$sql .="                       SELECT";
		$sql .="                               M01_TODOUFUKEN_CD";
		$sql .="                               ,M01_SHIKUCHOUSON_CD";
		$sql .="                               ,M01_IDO";
		$sql .="                               ,M01_KEIDO";
		$sql .="                           FROM";
		$sql .="                               M01JYUUSHO";
		$sql .="                           WHERE";
		$sql .="                               M01_TODOUFUKEN_CD = :PrefCd";
		$sql .="                               AND M01_SHIKUCHOUSON_CD = :CityCd";
		$sql .="                               AND M01_CHOUMEI_CD = :TownCd";
		$sql .="                               AND M01_CHOUMOKU_CD = :ChoumokuCd";
		$sql .="                           GROUP BY";
		$sql .="                               M01_TODOUFUKEN_CD";
		$sql .="                               ,M01_SHIKUCHOUSON_CD";
		$sql .="                               ,M01_IDO";
		$sql .="                               ,M01_KEIDO";
		$sql .="                   ) B";
		$sql .="               WHERE";
		$sql .="                   A.M01_TODOUFUKEN_CD = B.M01_TODOUFUKEN_CD";
		$sql .="                   AND A.M01_SHIKUCHOUSON_CD = B.M01_SHIKUCHOUSON_CD";
		$sql .="                   AND A.M01_IDO = B.M01_IDO";
		$sql .="                   AND A.M01_KEIDO = B.M01_KEIDO";
		$sql .="           UNION";
		$sql .="           SELECT";
		$sql .="                   A.M01_TODOUFUKEN_CD";
		$sql .="                   ,A.M01_SHIKUCHOUSON_CD";
		$sql .="                   ,A.M01_CHOUMEI_CD";
		$sql .="                   ,A.M01_CHOUMOKU_CD";
		$sql .="               FROM";
		$sql .="                   M01JYUUSHO A";
		$sql .="                   ,(";
		$sql .="                       SELECT";
		$sql .="                               M01_TODOUFUKEN_CD";
		$sql .="                               ,M01_SHIKUCHOUSON_CD";
		$sql .="                               ,M01_CHOUMEI_CD";
		$sql .="                               ,M01_IDO";
		$sql .="                               ,M01_KEIDO";
		$sql .="                           FROM";
		$sql .="                               M01JYUUSHO";
		$sql .="                           WHERE";
		$sql .="                               M01_TODOUFUKEN_CD = :PrefCd";
		$sql .="                               AND M01_SHIKUCHOUSON_CD = :CityCd";
		$sql .="                               AND M01_CHOUMEI_CD = :TownCd";
		$sql .="                               AND M01_CHOUMOKU_CD = :ChoumokuCd";
		$sql .="                           GROUP BY";
		$sql .="                               M01_TODOUFUKEN_CD";
		$sql .="                               ,M01_SHIKUCHOUSON_CD";
		$sql .="                               ,M01_CHOUMEI_CD";
		$sql .="                               ,M01_IDO";
		$sql .="                               ,M01_KEIDO";
		$sql .="                   ) B";
		$sql .="               WHERE";
		$sql .="                   A.M01_TODOUFUKEN_CD = B.M01_TODOUFUKEN_CD";
		$sql .="                   AND A.M01_SHIKUCHOUSON_CD = B.M01_SHIKUCHOUSON_CD";
		$sql .="                   AND A.M01_CHOUMEI_CD = B.M01_CHOUMEI_CD";
		$sql .="                   AND A.M01_IDO = B.M01_IDO";
		$sql .="                   AND A.M01_KEIDO = B.M01_KEIDO";
		$sql .="       )";

		return $sql;

	}

	/**
	 * 地図検索用WHERE句取得
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getWhereForChizusearch($params,&$db_params)
	{

		$sql  ="";
		$sql .="   WHERE";
		$sql .="       (";
		$sql .="           T03.T03_TODOUFUKEN_CD = M01_TODOUFUKEN_CD";
		$sql .="           AND T03.T03_SHIKUCHOUSON_CD = M01_SHIKUCHOUSON_CD";
		$sql .="           AND T03.T03_CHOUMEI_CD = M01_CHOUMEI_CD";
		$sql .="           AND T03.T03_CHOUMOKU_CD = M01_CHOUMOKU_CD";
		$sql .="       )";
		$sql .="       AND M05.M05_TENPO_CD = T03.T03_TENPO_CD ";
		if(array_key_exists("OemCd",$params) && $params['OemCd'] != ""){
			// OEMコードが8桁（店舗）
			if(strlen($params['OemCd']) == 8){
				$db_params['OemCd'] = $params["OemCd"];
				$sql .="     AND M05.M05_TENPO_CD  = : OemCd ";
			// OEMコードが4桁（法人）
			}else if(strlen($params['OemCd']) == 4){
				$db_params['OemCd'] = $params["OemCd"];
				$sql .="     AND M05.M05_HOUZIN_CD = : OemCd ";
			}
		}

		return $sql;

	}
}